<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page trimDirectiveWhitespaces="true"%>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>
<%@ page import = "java.sql.PreparedStatement" %>

<% 
//set Database Connection
	String hostProps = "";
	String usernameProps  = "";
	String passwordProps  = "";
	String databaseProps = "";
	
	try {
		//get current path
		ServletContext servletContext = request.getSession().getServletContext();
		
		InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
		Properties props = new Properties();
		
		props.load(input);

		hostProps  = props.getProperty("host");
		usernameProps  = props.getProperty("username");
		passwordProps  = props.getProperty("password");
		databaseProps = props.getProperty("database");
	} catch (Exception e) { 
		out.println(e);  
	}
	
	// connect database
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620" );
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
		} else {
			System.out.println("Database Connect Failed.");	
		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}
	
	System.out.println("@admin_employee_edit_personal_save Active");
	
	java.util.Date dt = new java.util.Date();

	java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US);

	String date_now = sdf.format(dt);
	
	String index = request.getParameter("index");
	System.out.println("index:"+index);
	String personal_id =  request.getParameter("personal_id");
	System.out.println("personal_id:"+personal_id);
	String title_th =  request.getParameter("title_th");
	String name_th =  request.getParameter("name_th");	
	String surname_th =  request.getParameter("surname_th");
	String name_en =  request.getParameter("name_en");	
	String surname_en =  request.getParameter("surname_en");
	String gender =  request.getParameter("gender");
	String marital_status = request.getParameter("marital_status"); //null
	String address =  request.getParameter("address"); //null
	String tel =  request.getParameter("tel"); //null'
	String email = request.getParameter("email");//null
	String facebook = request.getParameter("facebook");//null
	String picture = request.getParameter("picture");//null
	if(("null").equals(picture))
	{
		System.out.println("picture null detect");
		picture = null;
		
	}
	else{
		System.out.println("picture null not detect");
		
	}
	String department_id_sel = request.getParameter("department_id");
	String position = request.getParameter("position");
	
	String faculty_id = "1";
	String date_of_birth_in = request.getParameter("date_of_birth");
	String status = request.getParameter("status");
	

	String full_name_id = "";
	Date birth_date_dt  = sdf.parse(date_of_birth_in);

	
	String birth_date = sdf.format(birth_date_dt);
	
	System.out.println("date_now:"+date_now.toString());

	System.out.println("birth_date:"+birth_date);
	String dirty = "N";
	
	
	String key_index ="";
	String title_en = null;
	
    switch(title_th){
	
		case "นาย" : title_en ="Mr."; break;
		case "นางสาว" : title_en ="Miss"; break;
		case "นาง" : title_en="Mrs."; break;
	
	}
    
    System.out.println("title TH : "+title_th);
    System.out.println("title EN : "+title_en);
  
    
    ResultSet rs = connect.createStatement().executeQuery(" SELECT F.full_name_id "
    													 +" FROM full_name F"
    													 +" WHERE F.index='"+index+"'");
    
    while(rs.next()){

		full_name_id = rs.getString("full_name_id");   	
    }
    								
    try{
    	// update full_name
    	connect.createStatement().executeUpdate("UPDATE `full_name` SET  "
				+ " `title_th`='"+title_th+"', `title_en`='"+title_en+"', "
				+ " `name_th`='"+name_th+"', `name_en`='"+name_en+"',  "
				+ " `surname_th`='"+surname_th+"', `surname_en`='"+surname_en+"' "
				+ " WHERE full_name_id='"+full_name_id+"'");
    	
    	try{
			// update people
			connect.createStatement().executeUpdate("UPDATE `people` SET `personal_id`='"+personal_id+"', `gender`='"+gender+"', " 
											+ " `marital_status`='"+marital_status+"', `date_of_birth`='"+date_of_birth_in+"', "
											+ " `phone_number`='"+tel+"', `address`='"+address+"', "
											+ " `email`='"+email+"', `facebook`='"+facebook+"'" 
											+ " WHERE `index`='"+index+"'");
    		try{
    			String sql_cmd_c = "UPDATE `employee` SET "+
    					"`picture`='"+picture+"',`faculty_id`='"+faculty_id+"',`department_id`='"+department_id_sel+"'"+
    					",`status`='"+status+"',`position`='"+position+"'"+
    				    " WHERE `index`='"+index+"'";
    			
    			System.out.println("sql_cmd_c:"+sql_cmd_c);
    			
    			connect.createStatement().executeUpdate(sql_cmd_c);
    									
    			/* Log file */						
				String log = "admin edit employee pesonal";
				Log.writeAdminFile(log);
				Log.writeAdminFile(sql_cmd_c);		
    			
    			out.print("success");
    			
    		}catch(Exception c){
    		   	System.out.println("Error Update lecturer");
    		   	out.print("fail");
    			c.printStackTrace();
    		}
    	
    	
    	
    	}catch(Exception b){
    	   	System.out.println("Error Update People");
    	   	out.print("fail");
    		b.printStackTrace();
    	}
    	
    	
    	
    	
    }catch(Exception a)
    {
    	System.out.println("Error Update full_name");
    	out.print("fail");
    	a.printStackTrace();
    }
    
    connect.close();
    	
%>